﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
//using Microsoft.SharePoint.Client;
using System.Net;
using System.Xml;
using System.Data.SQLite;
using System.Net.Mail;
using System.Net.Mime;

namespace Testmatrix.Console
{
    public partial class TestForm : System.Windows.Forms.Form
    {
        public TestForm()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            SendData();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            string fileName = "data_" + Guid.NewGuid().ToString() + ".sqlite";

            SQLiteConnection.CreateFile(@"c:\temp\" + fileName);


            string sql = "CREATE TABLE tblBugList(" +
                    "ID int IDENTITY(1,1) NOT NULL," +
                    "ProjectName nvarchar(100) NOT NULL," +
                    "ItemID int NOT NULL," +
                    "AssignedTo nvarchar(100) NOT NULL," +
                    "State nvarchar(50) NOT NULL," +
                    "Severity nvarchar(50) NULL," +
                    "Priority nvarchar(50) NULL," +
                    "FoundInEnvironment nvarchar(50) NULL," +
                    "CreatedDate datetime NULL," +
                    "ResolvedDate datetime NULL," +
                    "ClosedDate datetime NULL," +
                    "ProjectFriendName nvarchar(100) NULL," +
                    "BugType nvarchar(100) NULL," +
                    "CreatedBy nvarchar(100) NULL," +
                    "ResolvedBy nvarchar(100) NULL)";

            string sqliteConnStr = @"Data Source=c:\temp\" + fileName;

            SQLiteConnection sqliteConn = new SQLiteConnection(sqliteConnStr);
            
                SQLiteCommand sqliteCmd = new SQLiteCommand(sql, sqliteConn);
                
                    sqliteConn.Open();
                    sqliteCmd.ExecuteNonQuery();
                    sqliteCmd.Dispose();
                    sqliteConn.Close();
        }

        private static string SqliteFileName = "data_" + Guid.NewGuid().ToString() + ".sqlite";
        private static string SoruceConnStr = "Server=EDVXMQASQLCT02;Database=MSCISDashboard;Trusted_Connection=True;";
        private static string sqliteConnStr = @"Data Source=c:\temp\" + SqliteFileName;

        private void createSqliteDB()
        {
            string sql =               
                    "CREATE TABLE tblBugList( " +
                    "ID int NOT NULL, " +
                    "ItemID int NOT NULL, " +
                    "ProjectName nvarchar(200) NULL, " +
                    "AssignedTo nvarchar(200) NULL, " +
                    "State nvarchar(200)  NULL, " +
                    "Severity nvarchar(200) NULL, " +
                    "Priority nvarchar(200) NULL, " +
                    "FoundInEnvironment nvarchar(200) NULL, " +
                    "CreatedDate datetime NULL, " +
                    "ResolvedDate datetime NULL," +
                    "ClosedDate datetime NULL," +
                    "ProjectFriendName nvarchar(200) NULL," +
                    "BugType nvarchar(200) NULL," +
                    "CreatedBy nvarchar(200) NULL," +
                    "ResolvedBy nvarchar(200) NULL);" + Environment.NewLine;

            sql += 
                    "CREATE TABLE tblTaskList( " +
                    "ID int NOT NULL, " +
                    "ItemID int NOT NULL, " +
                    "AreaPath nvarchar(200) NULL, " +
                    "IterationPath nvarchar(200) NULL, " +
                    "ProjectName nvarchar(200) NULL, " +
                    "State nvarchar(200)  NULL, " +
                    "Priority nvarchar(200) NULL, " +
                    "AssignedTo nvarchar(200) NULL, " +
                    "CreatedBy nvarchar(200) NULL," +
                    "CreatedDate datetime NULL, " +
                    "OriginalEstimate float NULL, " +
                    "RemainingWork float NULL, " +
                    "CompletedWork float NULL, " +
                    "ProjectFriendName nvarchar(200) NULL);" + Environment.NewLine;


            sql +=
                    "CREATE TABLE tblTestCaseList( " +
                    "ID int NOT NULL, " +
                    "ItemID int NOT NULL, " +
                    "ProjectName nvarchar(200) NULL, " +
                    "AreaPath nvarchar(200) NULL, " +
                    "IterationPath nvarchar(200) NULL, " +
                    "State nvarchar(200)  NULL, " +
                    "Priority nvarchar(200) NULL, " +
                    "AutomationStatus nvarchar(200) NULL, " +
                    "AssignedTo nvarchar(200) NULL, " +
                    "CreatedBy nvarchar(200) NULL," +
                    "CreatedDate datetime NULL, " +
                    "ClosedBy nvarchar(200) NULL," +
                    "Class nvarchar(200) NULL," +
                    "ClosedDate datetime NULL," +
                    "Frequency nvarchar(200) NULL, " +
                    "ProjectFriendName nvarchar(200) NULL)" + Environment.NewLine;

            SQLiteConnection sqliteConn = new SQLiteConnection(sqliteConnStr);
            SQLiteCommand sqliteCmd = new SQLiteCommand(sql, sqliteConn);
            sqliteConn.Open();
            sqliteCmd.ExecuteNonQuery();
            sqliteCmd.Dispose();
        }

        private DataSet GetSourceData(string str)
        {
            DataSet ds = new DataSet();

            SqlDataAdapter da = new SqlDataAdapter(str, SoruceConnStr);
            da.Fill(ds);

            return ds;
        }

        private void FillData()
        {
            string sql = "";
            
            //Fill tblBugList
            var strBugList = "select * from tblbuglist (nolock)";
            DataSet dsBugList = GetSourceData(strBugList);

            int rowCount = dsBugList.Tables[0].Rows.Count;
            int i = 0;
            while (i < rowCount)
            {
                sql = "insert into tblBugList ";

                for (int m = 0; m < 100; m++)
                {
                    DataRow row = dsBugList.Tables[0].Rows[i];
                    string ID = "select '" + Convert.ToInt32(row["ID"]).ToString() + "', ";
                    string ProjectName = "'" + row["ProjectName"].ToString() + "', ";
                    string ItemID = "'" + Convert.ToInt32(row["ItemID"]).ToString() + "', ";
                    string AssignedTo = "'" + row["AssignedTo"].ToString() + "', ";
                    string State = "'" + row["State"].ToString() + "', ";
                    string Severity = row["Severity"] == DBNull.Value ? "NULL, " : "'" + row["Severity"].ToString() + "', ";
                    string Priority = row["Priority"] == DBNull.Value ? "NULL, " : "'" + row["Priority"].ToString() + "', ";
                    string FoundInEnvironment = row["FoundInEnvironment"] == DBNull.Value ? "NULL, " : "'" + row["FoundInEnvironment"].ToString() + "', ";
                    string CreatedDate = row["CreatedDate"] == DBNull.Value ? "NULL, " : "'" + Convert.ToDateTime(row["CreatedDate"]).ToString() + "', ";
                    string ResolvedDate = row["ResolvedDate"] == DBNull.Value ? "NULL, " : "'" + Convert.ToDateTime(row["ResolvedDate"]).ToString() + "', ";
                    string ClosedDate = row["ClosedDate"] == DBNull.Value ? "NULL, " : "'" + Convert.ToDateTime(row["ClosedDate"]).ToString() + "', ";
                    string ProjectFriendName = row["ProjectFriendName"] == DBNull.Value ? "NULL, " : "'" + row["ProjectFriendName"].ToString() + "', ";
                    string BugType = row["BugType"] == DBNull.Value ? "NULL, " : "'" + row["BugType"].ToString() + "', ";
                    string CreatedBy = row["CreatedBy"] == DBNull.Value ? "NULL, " : "'" + row["CreatedBy"].ToString() + "', ";
                    string ResolvedBy = row["ResolvedBy"] == DBNull.Value ? "NULL   union all " : "'" + row["ResolvedBy"].ToString() + "'   union all";

                    sql += ID + ProjectName + ItemID + AssignedTo + State + Severity + Priority + FoundInEnvironment + CreatedDate + ResolvedDate + ClosedDate + ProjectFriendName + BugType + CreatedBy + ResolvedBy + Environment.NewLine;

                    i++;
                    if (i == rowCount)
                        break;
                }

                sql = sql.Substring(0, sql.Length - 12);
                SQLiteConnection sqliteConn = new SQLiteConnection(sqliteConnStr);
                SQLiteCommand sqliteCmd = new SQLiteCommand(sql, sqliteConn);

                sqliteConn.Open();
                sqliteCmd.ExecuteNonQuery();
                sqliteConn.Close();
                sqliteCmd.Dispose();
            }
            //Fill tblTaskList
            var strTaskList = "select * from tblTaskList (nolock)";
            DataSet dsTaskList = GetSourceData(strTaskList);

            rowCount = dsTaskList.Tables[0].Rows.Count;
            i = 0;
            while (i < rowCount)
            {
                sql = "insert into tblTaskList ";

                for (int m = 0; m < 100; m++)
                {
                    DataRow row = dsTaskList.Tables[0].Rows[i];
                    string ID = "select '" + Convert.ToInt32(row["ID"]).ToString() + "', ";
                    string ItemID = "'" + Convert.ToInt32(row["ItemID"]).ToString() + "', ";
                    string AreaPath = "'" + row["AreaPath"].ToString() + "', ";
                    string IterationPath = "'" + row["IterationPath"].ToString() + "', ";
                    string ProjectName = "'" + row["ProjectName"].ToString() + "', ";                    
                    string State = "'" + row["State"].ToString() + "', ";
                    string Priority = row["Priority"] == DBNull.Value ? "NULL, " : "'" + row["Priority"].ToString() + "', ";
                    string AssignedTo = "'" + row["AssignedTo"].ToString() + "', ";
                    string CreatedBy = row["CreatedBy"] == DBNull.Value ? "NULL, " : "'" + row["CreatedBy"].ToString() + "', ";
                    string CreatedDate = row["CreatedDate"] == DBNull.Value ? "NULL, " : "'" + Convert.ToDateTime(row["CreatedDate"]).ToString() + "', ";
                    string OriginalEstimate = row["OriginalEstimate"] == DBNull.Value ? "NULL, " : "'" + Convert.ToDouble(row["OriginalEstimate"]).ToString() + "', ";
                    string RemainingWork = row["RemainingWork"] == DBNull.Value ? "NULL, " : "'" + Convert.ToDouble(row["RemainingWork"]).ToString() + "', ";
                    string CompletedWork = row["CompletedWork"] == DBNull.Value ? "NULL, " : "'" + Convert.ToDouble(row["CompletedWork"]).ToString() + "', ";
                    string ProjectFriendName = row["ProjectFriendName"] == DBNull.Value ? "NULL, " : "'" + row["ProjectFriendName"].ToString() + "'   union all";

                    sql += ID + ItemID + AreaPath + IterationPath + ProjectName + State + Priority + AssignedTo + CreatedBy + CreatedDate + OriginalEstimate + RemainingWork + CompletedWork + ProjectFriendName + Environment.NewLine;

                    i++;
                    if (i == rowCount)
                        break;
                }

                sql = sql.Substring(0, sql.Length - 12);
                SQLiteConnection sqliteConn = new SQLiteConnection(sqliteConnStr);
                SQLiteCommand sqliteCmd = new SQLiteCommand(sql, sqliteConn);
                sqliteConn.Open();
                sqliteCmd.ExecuteNonQuery();
                sqliteConn.Close();
                sqliteCmd.Dispose();
            }
            //Fill tblTestCaseList
            var strTestCaseList = "select * from tblTestCaseList (nolock)";
            DataSet dsTestCaseList = GetSourceData(strTestCaseList);

            rowCount = dsTestCaseList.Tables[0].Rows.Count;
            i = 0;
            while (i < rowCount)
            {
                sql = "insert into tblTestCaseList ";

                for (int m = 0; m < 100; m++)
                {
                    DataRow row = dsTestCaseList.Tables[0].Rows[i];
                    string ID = "select '" + Convert.ToInt32(row["ID"]).ToString() + "', ";
                    string ItemID = "'" + Convert.ToInt32(row["ItemID"]).ToString() + "', ";
                    string ProjectName = "'" + row["ProjectName"].ToString() + "', ";
                    string AreaPath = "'" + row["AreaPath"].ToString() + "', ";
                    string IterationPath = "'" + row["IterationPath"].ToString() + "', ";
                    string State = "'" + row["State"].ToString() + "', ";
                    string Priority = row["Priority"] == DBNull.Value ? "NULL, " : "'" + row["Priority"].ToString() + "', ";
                    string AutomationStatus = "'" + row["AutomationStatus"].ToString() + "', ";
                    string AssignedTo = "'" + row["AssignedTo"].ToString() + "', ";
                    string CreatedBy = row["CreatedBy"] == DBNull.Value ? "NULL, " : "'" + row["CreatedBy"].ToString() + "', ";
                    string CreatedDate = row["CreatedDate"] == DBNull.Value ? "NULL, " : "'" + Convert.ToDateTime(row["CreatedDate"]).ToString() + "', ";
                    string ClosedBy = row["ClosedBy"] == DBNull.Value ? "NULL, " : "'" + row["ClosedBy"].ToString() + "', ";
                    string Class = row["Class"] == DBNull.Value ? "NULL, " : "'" + row["Class"].ToString() + "', ";
                    string ClosedDate = row["ClosedDate"] == DBNull.Value ? "NULL, " : "'" + Convert.ToDateTime(row["ClosedDate"]).ToString() + "', ";
                    string Frequency = row["Frequency"] == DBNull.Value ? "NULL, " : "'" + row["Frequency"].ToString() + "', ";
                    string ProjectFriendName = row["ProjectFriendName"] == DBNull.Value ? "NULL, " : "'" + row["ProjectFriendName"].ToString() + "'   union all";

                    sql += ID + ItemID + ProjectName + AreaPath + IterationPath + State + Priority + AutomationStatus + AssignedTo + CreatedBy + CreatedDate + ClosedBy + Class + ClosedDate + Frequency + ProjectFriendName + Environment.NewLine;

                    i++;
                    if (i == rowCount)
                        break;
                }

                sql = sql.Substring(0, sql.Length - 12);

                SQLiteConnection sqliteConn = new SQLiteConnection(sqliteConnStr);
                SQLiteCommand sqliteCmd = new SQLiteCommand(sql, sqliteConn);
                sqliteConn.Open();
                sqliteCmd.ExecuteNonQuery();
                sqliteConn.Close();
                sqliteCmd.Dispose();
            }
        }

        private void SendEmail()
        {

            var strMailList = "select * from tblmaillist (nolock)";
            DataSet dsMailList = GetSourceData(strMailList);

            MailMessage msg = new MailMessage();

            //msg.From = new MailAddress("v-dennyl@microsoft.com");
            msg.From = new MailAddress("v-toxue@microsoft.com");

            foreach (DataRow row in dsMailList.Tables[0].Rows)
            {
                string mail = row["Email"].ToString();
                msg.To.Add(mail);
            }


            msg.Subject = "TMDailyUpdate:" + DateTime.Now.ToString("yyyyMMddhhmmss");
            msg.SubjectEncoding = System.Text.Encoding.UTF8;

            msg.Body = "Test Matrix Daily Update.";
            msg.BodyEncoding = System.Text.Encoding.UTF8;

            msg.IsBodyHtml = false;
            msg.Priority = MailPriority.High;

            var attach = new Attachment(@"c:\temp\" + SqliteFileName);
            msg.Attachments.Add(attach);

            SmtpClient client = new SmtpClient();
            client.Port = 25;
            client.Host = "smtphost.redmond.corp.microsoft.com";

            client.UseDefaultCredentials = true;
            client.Send(msg);
        }

        private void button3_Click(object sender, EventArgs e)
        {
            createSqliteDB();
            FillData();
            //SendEmail();
            MessageBox.Show("completed..");
        }

        private void SendData()
        {
            var sqlConnStr = "Server=EDVXMQASQLCT02;Database=MSCISDashboard;Trusted_Connection=True;";

            var sql = "select * from tblbuglist (nolock)";

            SqlDataAdapter da = new SqlDataAdapter(sql, sqlConnStr);

            DataSet ds = new DataSet();

            da.Fill(ds);

            var ds2 = new DataSet();
            sql = "select * from tblmaillist (nolock)";

            da = new SqlDataAdapter(sql, sqlConnStr);

            da.Fill(ds2);

            string fileName = "data_" + Guid.NewGuid().ToString() + ".sqlite";

            sql = "CREATE TABLE tblBugList(" +
                    "ID int NOT NULL," +
                    "ProjectName nvarchar(100) NOT NULL," +
                    "ItemID int NOT NULL," +
                    "AssignedTo nvarchar(100) NOT NULL," +
                    "State nvarchar(50) NOT NULL," +
                    "Severity nvarchar(50) NULL," +
                    "Priority nvarchar(50) NULL," +
                    "FoundInEnvironment nvarchar(50) NULL," +
                    "CreatedDate datetime NULL," +
                    "ResolvedDate datetime NULL," +
                    "ClosedDate datetime NULL," +
                    "ProjectFriendName nvarchar(100) NULL," +
                    "BugType nvarchar(100) NULL," +
                    "CreatedBy nvarchar(100) NULL," +
                    "ResolvedBy nvarchar(100) NULL)";

            string sqliteConnStr = @"Data Source=c:\temp\" + fileName;

            SQLiteConnection sqliteConn = new SQLiteConnection(sqliteConnStr);
            SQLiteCommand sqliteCmd = new SQLiteCommand(sql, sqliteConn);

            sqliteConn.Open();
            sqliteCmd.ExecuteNonQuery();
            sqliteCmd.Dispose();

            int rowCount = ds.Tables[0].Rows.Count;
            int i = 0;


            while (i < rowCount)
            {
                sql = "insert into tblBugList ";

                for (int m = 0; m < 100; m++)
                {
                    DataRow row = ds.Tables[0].Rows[i];


                    string ID = "select '" + Convert.ToInt32(row["ID"]).ToString() + "', ";
                    string ProjectName = "'" + row["ProjectName"].ToString() + "', ";
                    string ItemID = "'" + Convert.ToInt32(row["ItemID"]).ToString() + "', ";
                    string AssignedTo = "'" + row["AssignedTo"].ToString() + "', ";
                    string State = "'" + row["State"].ToString() + "', ";

                    string Severity = row["Severity"] == DBNull.Value ? "NULL, " : "'" + row["Severity"].ToString() + "', ";
                    string Priority = row["Priority"] == DBNull.Value ? "NULL, " : "'" + row["Priority"].ToString() + "', ";
                    string FoundInEnvironment = row["FoundInEnvironment"] == DBNull.Value ? "NULL, " : "'" + row["FoundInEnvironment"].ToString() + "', ";
                    string CreatedDate = row["CreatedDate"] == DBNull.Value ? "NULL, " : "'" + Convert.ToDateTime(row["CreatedDate"]).ToString() + "', ";
                    string ResolvedDate = row["ResolvedDate"] == DBNull.Value ? "NULL, " : "'" + Convert.ToDateTime(row["ResolvedDate"]).ToString() + "', ";

                    string ClosedDate = row["ClosedDate"] == DBNull.Value ? "NULL, " : "'" + Convert.ToDateTime(row["ClosedDate"]).ToString() + "', ";
                    string ProjectFriendName = row["ProjectFriendName"] == DBNull.Value ? "NULL, " : "'" + row["ProjectFriendName"].ToString() + "', ";
                    string BugType = row["BugType"] == DBNull.Value ? "NULL, " : "'" + row["BugType"].ToString() + "', ";
                    string CreatedBy = row["CreatedBy"] == DBNull.Value ? "NULL, " : "'" + row["CreatedBy"].ToString() + "', ";
                    //string ResolvedBy = row["ResolvedBy"] == DBNull.Value ? "NULL )" : "'" + row["ResolvedBy"].ToString() + "' )";
                    string ResolvedBy = row["ResolvedBy"] == DBNull.Value ? "NULL   union all " : "'" + row["ResolvedBy"].ToString() + "'   union all";

                    sql += ID + ProjectName + ItemID + AssignedTo + State + Severity + Priority + FoundInEnvironment + CreatedDate + ResolvedDate + ClosedDate + ProjectFriendName + BugType + CreatedBy + ResolvedBy + Environment.NewLine;


                    i++;

                    if (i == rowCount)
                        break;
                }

                sql = sql.Substring(0, sql.Length - 12);

                sqliteCmd = new SQLiteCommand(sql, sqliteConn);
                sqliteCmd.ExecuteNonQuery();
                sqliteCmd.Dispose();
            }




            sqliteCmd.Dispose();

            sqliteConn.Close();
            sqliteConn.Dispose();



            MailMessage msg = new MailMessage();


            //msg.From = new MailAddress("v-dennyl@microsoft.com");
            msg.From = new MailAddress("v-toxue@microsoft.com");

            foreach (DataRow row in ds2.Tables[0].Rows)
            {
                string mail = row["Email"].ToString();
                msg.To.Add(mail);
            }


            msg.Subject = "TMDailyUpdate:" + DateTime.Now.ToString("yyyyMMddhhmmss");
            msg.SubjectEncoding = System.Text.Encoding.UTF8;

            msg.Body = "Test Matrix Daily Update.";
            msg.BodyEncoding = System.Text.Encoding.UTF8;

            msg.IsBodyHtml = false;
            msg.Priority = MailPriority.High;

            var attach = new Attachment(@"c:\temp\" + fileName);
            msg.Attachments.Add(attach);

            SmtpClient client = new SmtpClient();
            client.Port = 25;
            client.Host = "smtphost.redmond.corp.microsoft.com";

            client.UseDefaultCredentials = true;
            client.Send(msg);
        }



        

        private DataSet GetSourceAzureData(string str)
        {
            DataSet ds = new DataSet();

            SqlDataAdapter da = new SqlDataAdapter(str, DBHelper.SourceConnectionString);
            da.Fill(ds);

            return ds;
        }

        private void FillAzureData()
        {
            string sql = "";

            //Fill tblBugList
            var strBugList = "select * from tblbuglist (nolock)";
            DataSet dsBugList = GetSourceAzureData(strBugList);

            int rowCount = dsBugList.Tables[0].Rows.Count;
            int i = 0;
            while (i < rowCount)
            {
                sql = "insert into tblBugList (ID, ProjectName,ItemID, AssignedTo, State, Severity, Priority, FoundInEnvironment, CreatedDate, ResolvedDate, ClosedDate, ProjectFriendName, BugType, CreatedBy, ResolvedBy)";

                for (int m = 0; m < 100; m++)
                {
                    DataRow row = dsBugList.Tables[0].Rows[i];
                    string ID = "select '" + Convert.ToInt32(row["ID"]).ToString() + "', ";
                    string ProjectName = "'" + row["ProjectName"].ToString() + "', ";
                    string ItemID = "'" + Convert.ToInt32(row["ItemID"]).ToString() + "', ";
                    string AssignedTo = "'" + row["AssignedTo"].ToString() + "', ";
                    string State = "'" + row["State"].ToString() + "', ";
                    string Severity = row["Severity"] == DBNull.Value ? "NULL, " : "'" + row["Severity"].ToString() + "', ";
                    string Priority = row["Priority"] == DBNull.Value ? "NULL, " : "'" + row["Priority"].ToString() + "', ";
                    string FoundInEnvironment = row["FoundInEnvironment"] == DBNull.Value ? "NULL, " : "'" + row["FoundInEnvironment"].ToString() + "', ";
                    string CreatedDate = row["CreatedDate"] == DBNull.Value ? "NULL, " : "'" + Convert.ToDateTime(row["CreatedDate"]).ToString() + "', ";
                    string ResolvedDate = row["ResolvedDate"] == DBNull.Value ? "NULL, " : "'" + Convert.ToDateTime(row["ResolvedDate"]).ToString() + "', ";
                    string ClosedDate = row["ClosedDate"] == DBNull.Value ? "NULL, " : "'" + Convert.ToDateTime(row["ClosedDate"]).ToString() + "', ";
                    string ProjectFriendName = row["ProjectFriendName"] == DBNull.Value ? "NULL, " : "'" + row["ProjectFriendName"].ToString() + "', ";
                    string BugType = row["BugType"] == DBNull.Value ? "NULL, " : "'" + row["BugType"].ToString() + "', ";
                    string CreatedBy = row["CreatedBy"] == DBNull.Value ? "NULL, " : "'" + row["CreatedBy"].ToString() + "', ";
                    string ResolvedBy = row["ResolvedBy"] == DBNull.Value ? "NULL   union all " : "'" + row["ResolvedBy"].ToString() + "'   union all";

                    sql += ID + ProjectName + ItemID + AssignedTo + State + Severity + Priority + FoundInEnvironment + CreatedDate + ResolvedDate + ClosedDate + ProjectFriendName + BugType + CreatedBy + ResolvedBy + Environment.NewLine;

                    i++;
                    if (i == rowCount)
                        break;
                }

                sql = sql.Substring(0, sql.Length - 12);
                try
                {
                    DBHelper.ExecuteNonQuery(DBHelper.TargetConnectionString, CommandType.Text, sql, null);
                }
                catch (Exception ex)
                {
                    IOHelper.Log("FillAzureData() ====>  [step] Fill tblBugList ====> " + ex.ToString(), DBHelper.fileLog);
                    return;
                }
            }
            //Fill tblTaskList
            var strTaskList = "select * from tblTaskList (nolock)";
            DataSet dsTaskList = GetSourceData(strTaskList);

            rowCount = dsTaskList.Tables[0].Rows.Count;
            i = 0;
            while (i < rowCount)
            {
                sql = "insert into tblTaskList (ID, ItemID, AreaPath, IterationPath, ProjectName, State, Priority, AssignedTo, CreatedBy, CreatedDate, OriginalEstimate, RemainingWork, CompletedWork, ProjectFriendName)";

                for (int m = 0; m < 100; m++)
                {
                    DataRow row = dsTaskList.Tables[0].Rows[i];
                    string ID = "select '" + Convert.ToInt32(row["ID"]).ToString() + "', ";
                    string ItemID = "'" + Convert.ToInt32(row["ItemID"]).ToString() + "', ";
                    string AreaPath = "'" + row["AreaPath"].ToString() + "', ";
                    string IterationPath = "'" + row["IterationPath"].ToString() + "', ";
                    string ProjectName = "'" + row["ProjectName"].ToString() + "', ";
                    string State = "'" + row["State"].ToString() + "', ";
                    string Priority = row["Priority"] == DBNull.Value ? "NULL, " : "'" + row["Priority"].ToString() + "', ";
                    string AssignedTo = "'" + row["AssignedTo"].ToString() + "', ";
                    string CreatedBy = row["CreatedBy"] == DBNull.Value ? "NULL, " : "'" + row["CreatedBy"].ToString() + "', ";
                    string CreatedDate = row["CreatedDate"] == DBNull.Value ? "NULL, " : "'" + Convert.ToDateTime(row["CreatedDate"]).ToString() + "', ";
                    string OriginalEstimate = row["OriginalEstimate"] == DBNull.Value ? "NULL, " : "'" + Convert.ToDouble(row["OriginalEstimate"]).ToString() + "', ";
                    string RemainingWork = row["RemainingWork"] == DBNull.Value ? "NULL, " : "'" + Convert.ToDouble(row["RemainingWork"]).ToString() + "', ";
                    string CompletedWork = row["CompletedWork"] == DBNull.Value ? "NULL, " : "'" + Convert.ToDouble(row["CompletedWork"]).ToString() + "', ";
                    string ProjectFriendName = row["ProjectFriendName"] == DBNull.Value ? "NULL, " : "'" + row["ProjectFriendName"].ToString() + "'   union all";

                    sql += ID + ItemID + AreaPath + IterationPath + ProjectName + State + Priority + AssignedTo + CreatedBy + CreatedDate + OriginalEstimate + RemainingWork + CompletedWork + ProjectFriendName + Environment.NewLine;

                    i++;
                    if (i == rowCount)
                        break;
                }

                sql = sql.Substring(0, sql.Length - 12);
                try
                {
                    DBHelper.ExecuteNonQuery(DBHelper.TargetConnectionString, CommandType.Text, sql, null);
                }
                catch (Exception ex)
                {
                    IOHelper.Log("FillAzureData() ====>  [step] Fill tblTaskList ====> " + ex.ToString(), DBHelper.fileLog);
                    return;
                }
            }
            //Fill tblTestCaseList
            var strTestCaseList = "select * from tblTestCaseList (nolock)";
            DataSet dsTestCaseList = GetSourceData(strTestCaseList);

            rowCount = dsTestCaseList.Tables[0].Rows.Count;
            i = 0;
            while (i < rowCount)
            {
                sql = "insert into tblTestCaseList (ID, ItemID, ProjectName, AreaPath, IterationPath, State, Priority, AutomationStatus, AssignedTo, CreatedBy, CreatedDate, ClosedBy, Class, ClosedDate, Frequency, ProjectFriendName)";

                for (int m = 0; m < 100; m++)
                {
                    DataRow row = dsTestCaseList.Tables[0].Rows[i];
                    string ID = "select '" + Convert.ToInt32(row["ID"]).ToString() + "', ";
                    string ItemID = "'" + Convert.ToInt32(row["ItemID"]).ToString() + "', ";
                    string ProjectName = "'" + row["ProjectName"].ToString() + "', ";
                    string AreaPath = "'" + row["AreaPath"].ToString() + "', ";
                    string IterationPath = "'" + row["IterationPath"].ToString() + "', ";
                    string State = "'" + row["State"].ToString() + "', ";
                    string Priority = row["Priority"] == DBNull.Value ? "NULL, " : "'" + row["Priority"].ToString() + "', ";
                    string AutomationStatus = "'" + row["AutomationStatus"].ToString() + "', ";
                    string AssignedTo = "'" + row["AssignedTo"].ToString() + "', ";
                    string CreatedBy = row["CreatedBy"] == DBNull.Value ? "NULL, " : "'" + row["CreatedBy"].ToString() + "', ";
                    string CreatedDate = row["CreatedDate"] == DBNull.Value ? "NULL, " : "'" + Convert.ToDateTime(row["CreatedDate"]).ToString() + "', ";
                    string ClosedBy = row["ClosedBy"] == DBNull.Value ? "NULL, " : "'" + row["ClosedBy"].ToString() + "', ";
                    string Class = row["Class"] == DBNull.Value ? "NULL, " : "'" + row["Class"].ToString() + "', ";
                    string ClosedDate = row["ClosedDate"] == DBNull.Value ? "NULL, " : "'" + Convert.ToDateTime(row["ClosedDate"]).ToString() + "', ";
                    string Frequency = row["Frequency"] == DBNull.Value ? "NULL, " : "'" + row["Frequency"].ToString() + "', ";
                    string ProjectFriendName = row["ProjectFriendName"] == DBNull.Value ? "NULL, " : "'" + row["ProjectFriendName"].ToString() + "'   union all";

                    sql += ID + ItemID + ProjectName + AreaPath + IterationPath + State + Priority + AutomationStatus + AssignedTo + CreatedBy + CreatedDate + ClosedBy + Class + ClosedDate + Frequency + ProjectFriendName + Environment.NewLine;

                    i++;
                    if (i == rowCount)
                        break;
                }

                sql = sql.Substring(0, sql.Length - 12);

                try
                {
                    DBHelper.ExecuteNonQuery(DBHelper.TargetConnectionString, CommandType.Text, sql, null);
                }
                catch (Exception ex)
                {
                    IOHelper.Log("FillAzureData() ====>  [step] Fill tblTestCaseList ====> " + ex.ToString(), DBHelper.fileLog);
                    return;
                }
            }
        }

        private void button4_Click(object sender, EventArgs e)
        {
            CreateTableAzure();
            FillAzureData();
            MessageBox.Show("completed.");
        }

        private void CreateTableAzure()
        {
            string sql =
                    "IF OBJECT_ID('test20131025.BugListAzure') IS NULL " +
                    "CREATE TABLE test20131025.BugListAzure( " +
                    "ID int NOT NULL, " +
                    "ItemID int NOT NULL, " +
                    "ProjectName nvarchar(200) NULL, " +
                    "AssignedTo nvarchar(200) NULL, " +
                    "State nvarchar(200)  NULL, " +
                    "Severity nvarchar(200) NULL, " +
                    "Priority nvarchar(200) NULL, " +
                    "FoundInEnvironment nvarchar(200) NULL, " +
                    "CreatedDate datetime NULL, " +
                    "ResolvedDate datetime NULL," +
                    "ClosedDate datetime NULL," +
                    "ProjectFriendName nvarchar(200) NULL," +
                    "BugType nvarchar(200) NULL," +
                    "CreatedBy nvarchar(200) NULL," +
                    "ResolvedBy nvarchar(200) NULL);" + Environment.NewLine +
                    "if not exists(select 1 from sys.indexes where object_id=OBJECT_ID('test20131025.BugListAzure') and name='PK_BugListAzure_ID')" + Environment.NewLine +
                    "create clustered index PK_BugListAzure_ID ON test20131025.BugListAzure(ItemID);" + Environment.NewLine;

            //sql +=
            //        "IF OBJECT_ID('tblTaskList') IS NULL " +
            //        "CREATE TABLE tblTaskList( " +
            //        "ID int NOT NULL, " +
            //        "ItemID int NOT NULL, " +
            //        "AreaPath nvarchar(200) NULL, " +
            //        "IterationPath nvarchar(200) NULL, " +
            //        "ProjectName nvarchar(200) NULL, " +
            //        "State nvarchar(200)  NULL, " +
            //        "Priority nvarchar(200) NULL, " +
            //        "AssignedTo nvarchar(200) NULL, " +
            //        "CreatedBy nvarchar(200) NULL," +
            //        "CreatedDate datetime NULL, " +
            //        "OriginalEstimate float NULL, " +
            //        "RemainingWork float NULL, " +
            //        "CompletedWork float NULL, " +
            //        "ProjectFriendName nvarchar(200) NULL);" + Environment.NewLine+
            //        "if not exists(select 1 from sys.indexes where object_id=OBJECT_ID('tblTaskList') and name='PK_tblTaskList_ID')" + Environment.NewLine +
            //        "create clustered index PK_tblTaskList_ID ON tblTaskList(ID);" + Environment.NewLine;


            //sql +=
            //        "IF OBJECT_ID('tblTestCaseList') IS NULL " +
            //        "CREATE TABLE tblTestCaseList( " +
            //        "ID int NOT NULL, " +
            //        "ItemID int NOT NULL, " +
            //        "ProjectName nvarchar(200) NULL, " +
            //        "AreaPath nvarchar(200) NULL, " +
            //        "IterationPath nvarchar(200) NULL, " +
            //        "State nvarchar(200)  NULL, " +
            //        "Priority nvarchar(200) NULL, " +
            //        "AutomationStatus nvarchar(200) NULL, " +
            //        "AssignedTo nvarchar(200) NULL, " +
            //        "CreatedBy nvarchar(200) NULL," +
            //        "CreatedDate datetime NULL, " +
            //        "ClosedBy nvarchar(200) NULL," +
            //        "Class nvarchar(200) NULL," +
            //        "ClosedDate datetime NULL," +
            //        "Frequency nvarchar(200) NULL, " +
            //        "ProjectFriendName nvarchar(200) NULL)" + Environment.NewLine+
            //        "if not exists(select 1 from sys.indexes where object_id=OBJECT_ID('tblTestCaseList') and name='PK_tblTestCaseList_ID')" + Environment.NewLine +
            //        "create clustered index PK_tblTestCaseList_ID ON tblTestCaseList(ID);" + Environment.NewLine;
            try
            {
                DBHelper.ExecuteNonQuery(DBHelper.TargetConnectionString, CommandType.Text, sql, null);
            }
            catch (Exception ex)
            {
                IOHelper.Log("CreateTableAzure ====> " + ex.ToString(), DBHelper.fileLog);
                return;
            }
        }

        private void button5_Click(object sender, EventArgs e)
        {
            CreateTableAzure();

            DBHelper.ExecuteNonQuery(DBHelper.TargetConnectionString, CommandType.Text, "truncate table [test20131025_db].[test20131025].[BugListAzure]", null);

            string sql = "";

            //Fill tblBugList
            var strBugList = "select * from tblbuglist (nolock)";
            DataSet dsBugList = GetSourceAzureData(strBugList);

            int rowCount = dsBugList.Tables[0].Rows.Count;

            int i = 0;
            while (i < rowCount)
            {

                sql += "insert into test20131025.BugListAzure (Id,ProjectName,ItemID, AssignedTo, State, Severity, Priority, FoundInEnvironment, CreatedDate, ResolvedDate, ClosedDate, ProjectFriendName, BugType, CreatedBy, ResolvedBy)";

                for (int m = 0; m < 100; m++)
                {
                    DataRow row = dsBugList.Tables[0].Rows[i];
                    //string ID = "select '" + m.ToString() + "', ";
                    string ID = "select '" + Convert.ToInt32(row["ID"]).ToString() + "', ";
                    string ProjectName = "'" + row["ProjectName"].ToString() + "', ";

                    //string ProjectName = "select '" + row["ProjectName"].ToString() + "', ";
                    string ItemID = "'" + Convert.ToInt32(row["ItemID"]).ToString() + "', ";
                    string AssignedTo = "'" + row["AssignedTo"].ToString() + "', ";
                    string State = "'" + row["State"].ToString() + "', ";
                    string Severity = row["Severity"] == DBNull.Value ? "NULL, " : "'" + row["Severity"].ToString() + "', ";
                    string Priority = row["Priority"] == DBNull.Value ? "NULL, " : "'" + row["Priority"].ToString() + "', ";
                    string FoundInEnvironment = row["FoundInEnvironment"] == DBNull.Value ? "NULL, " : "'" + row["FoundInEnvironment"].ToString() + "', ";
                    string CreatedDate = row["CreatedDate"] == DBNull.Value ? "NULL, " : "'" + Convert.ToDateTime(row["CreatedDate"]).ToString() + "', ";
                    string ResolvedDate = row["ResolvedDate"] == DBNull.Value ? "NULL, " : "'" + Convert.ToDateTime(row["ResolvedDate"]).ToString() + "', ";
                    string ClosedDate = row["ClosedDate"] == DBNull.Value ? "NULL, " : "'" + Convert.ToDateTime(row["ClosedDate"]).ToString() + "', ";
                    string ProjectFriendName = row["ProjectFriendName"] == DBNull.Value ? "NULL, " : "'" + row["ProjectFriendName"].ToString() + "', ";
                    string BugType = row["BugType"] == DBNull.Value ? "NULL, " : "'" + row["BugType"].ToString() + "', ";
                    string CreatedBy = row["CreatedBy"] == DBNull.Value ? "NULL, " : "'" + row["CreatedBy"].ToString() + "', ";
                    string ResolvedBy = row["ResolvedBy"] == DBNull.Value ? "NULL   union all " : "'" + row["ResolvedBy"].ToString() + "'   union all";

                    //sql +=  ProjectName + ItemID + AssignedTo + State + Severity + Priority + FoundInEnvironment + CreatedDate + ResolvedDate + ClosedDate + ProjectFriendName + BugType + CreatedBy + ResolvedBy + Environment.NewLine;
                    sql += ID + ProjectName + ItemID + AssignedTo + State + Severity + Priority + FoundInEnvironment + CreatedDate + ResolvedDate + ClosedDate + ProjectFriendName + BugType + CreatedBy + ResolvedBy + Environment.NewLine;
                    i++;
                    if (i == rowCount)
                        break;
                }

                sql = sql.Substring(0, sql.Length - 12);
                try
                {
                    DBHelper.ExecuteNonQuery(DBHelper.TargetConnectionString, CommandType.Text, sql, null);
                    sql = "";
                }
                catch (Exception ex)
                {
                    IOHelper.Log("FillAzureData() ====>  [step] Fill tblBugList ====> " + ex.ToString(), DBHelper.fileLog);
                    return;
                }
            }

            MessageBox.Show("completed.");
        }
    }
}
